Copyright: Copyright 1993 Microsoft Corp.
Citation: Copyright 1993 Microsoft Corporation. All rights reserved.


 Excel 5 Tips

Prepared 01/21/94

 Excel 5 Tips

THE INFORMATION IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES EITHER EXPRESSED OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS, OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FORGOING EXCLUSION OR LIMITATION MAY NOT APPLY.


 Excel 5 Tips

 Excel 5 Tips

 XL5: Version 5.0 Uses Its Own Toolbar Settings File

 XL5: No Direct Visual Basic Equivalent for SOUND.PLAY()

 XL5: Print Selection Does Not Ignore Print Titles

 XL5: AutoFilter Drop-down List Is Limited to 251 Identifiers

 XL5: Cannot Drag, Cut, or Copy AutoFilter Drop-down List

 XL5: Cannot Generate Static Custom Dialog Boxes

 XL5: Computer Beeps, Cells Not Selected with PAGE UP/PAGE DOWN

 XL5: International Settings Not Updated Automatically

 XL5: Static and Dynamic Default Buttons

 XL5: Can Use Same Name to Define Object and Defined Name

 XL5: Cannot Use Name Box to Define 3-D Name

 XL5: Unexpected Results Editing Fields in PivotTable Wizard

 XL5: RemoveItem and RemoveAllItems Methods May Not Work

 XL5: Linked References Not Updated in Closed Worksheet

 XL5: Macro Cannot Scroll Chart Windows

 XL5 Err Msg: Redefine Scenario Based on Current Cell Values?

 XL5: Open Subroutines Run Before Referenced Subroutines

 XL5: PivotTable Wizard Does Not Automatically Refresh Data

 XL5: Print Area and Print Titles Boxes Empty in Print Preview

 XL5: Can't Use Varied Formats When Text Box Is Linked to Cell

 XL5: Excel 4.0 Menus Option Setting Not Saved

 XL5: Watch Pane Does Not Use Procedure Call Context

 XL5 Err Msg: "Must Exit Windows and Load SHARE.EXE. . ."

 XL5: Links Command Unavailable Unless Links Are External

 XL5: Unexpected Results When Pasting Linked Text Box

 XL5: Can't Reference Global Name on First Sheet

 XL5: Alert Function Does Not Beep in Excel Version 5.0

 XL5: Using Global and Local References in Formulas

 XL5: Using "Most Recently Used" Category in Function Wizard

 XL5: Copy Picture Does Not Copy Row or Column Headings

 XL5 Err Msg: Error in Procedure Causes "Cannot Find Procedure"

 XL5: Not All Worksheet Functions Supported As Application

 XL5: Cannot Add Sheet After Last Sheet in a Single Action

 XL5: Cannot Use Crosstab Add-In Macro in Worksheet Object

 XL5: Chart Number Format Not Updated If Data Source External

 XL5: Scatter Chart Line Disappears After Changing Series Axis

 XL5: Objects Setting Applies to Entire Workbook

 XL5: Unexpected Settings/Values If 4.0 Workbook Opened in 5.0

 XL5: Data in AutoFill Series Incremented Based on Selection

 XL5: Recording a Visual Basic Macro in an External Module


XL5: Version 5.0 Uses Its Own Toolbar Settings File

Article ID: Q105035

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

Microsoft Excel 5.0 automatically uses the version 5.0 toolbar

settings: it will not automatically use the Microsoft Excel version

4.0 toolbar file (EXCEL.XLB). In Microsoft Excel 5.0, a new toolbar

file (EXCEL5.XLB) is created when you add, delete, or modify the

built-in toolbars.

If you want to use the version 4.0 toolbar settings in Microsoft Excel

5.0, you must manually open the EXCEL.XLB file from within Microsoft

Excel version 5.0.

When you exit the current session of Microsoft Excel, the previous

Microsoft Excel version 4.0 settings will then be written to the

EXCEL5.XLB file. If this file does not already exist, a new one will

be created. If an EXCEL5.XLB file does already exist, the new

information will overwrite the information in the existing file.

REFERENCES

==========

"Visual Basic User's Guide," version 5.0, page 263

Additional reference words: 5.00


XL5: No Direct Visual Basic Equivalent for SOUND.PLAY()

Article ID: Q105202

--------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

--------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0, to play a sound file from disk in a

Visual Basic macro, use the ExecuteExcel4Macro method.

MORE INFORMATION

================

In a Microsoft Excel version 4.0 macro, to play a sound file directly

from a disk, use the SOUND.PLAY() macro function. For example, to play

a sound file called CHIMES.WAV, located in your Windows directory, you

would use the following line of macro code:

    =SOUND.PLAY(,"C:\WINDOWS\CHIMES.WAV")

In Microsoft Excel version 5.0, the nearest Visual Basic equivalent to

SOUND.PLAY() is SoundNote.Play. However, this function will only play

sound files that have been added to Microsoft Excel documents as sound

notes.

Because of this limitation, you must use the ExecuteExcel4Macro method

to play a sound file directly from disk in a Visual Basic macro. The

Visual Basic equivalent of the macro code above is:

 Sub PlaySound()

    Application.ExecuteExcel4Macro "SOUND.PLAY(,""C:\WINDOWS\CHIMES.WAV"")"

 End Sub

Note the use of double quotation marks in this macro. These quotation

marks are necessary because the first set of quotation marks are

removed before the command is executed.

Additional reference words: 5.00


XL5: Print Selection Does Not Ignore Print Titles

Article ID: Q105203

-----------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

-----------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0, print titles will always be printed if

they are defined.

This is by design.

To remove the print titles:

1. From the File menu, choose Page Setup.

2. Select the Sheet tab.

3. Under Print Titles, clear the Rows To Repeat At Top and Columns To

   Repeat At Left boxes.

4. Choose OK to accept the change.

MORE INFORMATION

================

In Microsoft Excel version 5.0, when you print your document, you can

select one of three print options under Print What in the Print dialog

box.

   This option         Prints the following

   -----------------------------------------------------------------

   Selection           The selected range (or selected sheets, if in

                       group edit mode) on the active sheet.

   Selected Sheets     The printable area of the active sheet (or

                       sheets, if in group edit mode) is printed.

   Entire Workbook     The printable area of all sheets in the

                       workbook is printed.

If you choose to print the selection only, Microsoft Excel ignores any

print area that you may have defined on any of your sheets and prints

only the currently selected area.

This situation does not apply to print titles. Microsoft Excel will

always print defined print titles regardless of which print option you

select.

Additional reference words: 5.00 print_titles


XL5: AutoFilter Drop-down List Is Limited to 251 Identifiers

Article ID: Q105322

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

---------------------------------------------------------------------

In Microsoft Excel version 5.0, when you use the AutoFilter command to

filter data, the resulting drop-down list of unique items is limited

to 251 identifiers. If the number of unique identifiers in a list is

extremely large, you may receive "Not Enough Memory" errors or the

system may hang.

Additional reference words: 5.00 crash respond


XL5: Cannot Drag, Cut, or Copy AutoFilter Drop-down List

Article ID: Q105412

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0, you cannot drag, cut and paste, or

copy and paste the drop-down lists that are created when you use the

AutoFilter to sort a list.

This is by design.

MORE INFORMATION

================

If you need to move the AutoFilter drop-down lists, you must move your

data and choose the AutoFilter command again.

Although it is not possible to drag, cut, or copy AutoFilter drop-down

lists, they will move properly if you insert or delete columns or rows

that are are to the left of or above your list range. For example, if

you have AutoFilter drop-down lists in cells E10:G10, and you delete

column A, the lists will shift one column to the left; similarly, if

you delete row 7, the lists will shift up one row.

Additional reference words: 5.00


XL5: Cannot Generate Static Custom Dialog Boxes

Article ID: Q105413

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0, it is not possible to generate a

static, or unmovable, custom dialog box.

MORE INFORMATION

================

By design of the custom dialog box system in Microsoft Excel version

5.0, it is not possible to generate a static dialog box. Therefore, you

cannot prevent a user from moving a dialog box by dragging the box's

title bar.

However, you can control the position of the dialog box when it is

initially displayed, or at any time in a Visual Basic procedure, by

using the DialogFrame.Left and DialogFrame.Top methods.

Additional reference words: 5.00 immobile nonmoveable


XL5: Computer Beeps, Cells Not Selected with PAGE UP/PAGE DOWN

Article ID: Q105414

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

The PAGE UP and PAGE DOWN keys may behave differently in Microsoft

Excel version 5.0 than they do in earlier versions of Microsoft

Excel. This differences in behavior is only apparent if you are near

the top (usually, rows 1-40) or the bottom (usually, rows 16340-16384)

of your worksheet.

When you use these keys in other locations, they function the same as

in earlier versions of Microsoft Excel.

MORE INFORMATION

================

Steps to Reproduce Situation

----------------------------

To see the difference in the way the PAGE UP key behaves, do either of

the following:

1. On a new worksheet, select cell A10. Cell A1 should be visible on

   the screen.

2. Press the PAGE UP key.

In Microsoft Excel 5.0, the computer will beep and cell A10 will

remain selected. In earlier versions of Microsoft Excel, cell A1 will

be selected.

-or-

1. Select cell A10 and scroll the worksheet so that cell A10 is in the

   upper-left cell of the screen.

2. Press the PAGE UP key.

In Microsoft Excel version 5.0, the worksheet will scroll so that

cell A1 is visible, but cell A10 will remain selected. In earlier

versions of Microsoft Excel, the worksheet will scroll up AND cell A1

will be selected.

To see the difference in the way the PAGE DOWN key behaves, do either

of the following:

1. In a new worksheet, select cell A16380. Cell A16384 should be

   visible on the screen.

2. Press the PAGE DOWN key.

In Microsoft Excel version 5.0, the computer will beep and cell A16380

will remain selected. In earlier versions of Microsoft Excel, cell

A16384 will be selected.

-or-

1. Select cell A16380 and scroll the worksheet vertically so that cell

   A16380 is in the lower-left cell (no part of cell A16381 should be

   visible).

2. Press the PAGE DOWN key.

In Microsoft Excel version 5.0, cell A16384 will be selected. In

earlier versions of Microsoft Excel, the worksheet will scroll down so

that cell A16384 is visible but cell A16380 will remain selected.

Additional reference words: 5.00


XL5: International Settings Not Updated Automatically

Article ID: Q105449

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

Changes you make to the International settings in the Microsoft

Windows Control Panel will not be reflected in the Module General tab

of the Options dialog box (from the Tools menu, choose Options) until

you change the Language/Country setting to English/(Custom).

This is by design of Microsoft Excel, version 5.0.

To use modified international settings in Microsoft Excel, do the

following:

1. In Microsoft Excel, choose Options from the Tools menu.

2. In the Options dialog box, select the Module General tab.

3. Under International, select English/(Custom) from the

   Language/Country list.

4. Choose OK.

MORE INFORMATION

================

In Microsoft Windows, you can change settings such as the standard

list separator character, currency character, currency number formats,

number formats, and date/time formats by using the International

dialog box in Microsoft Windows Control Panel. Changes that you make

are used in most applications that run in Microsoft Windows, including

Microsoft Excel.

However, Microsoft Excel assumes by default that you wish to use the

standard English/United States settings, even if you make changes to

the International settings in Control Panel.

REFERENCES

==========

"User's Guide," version 5.0, Chapter 34

Additional reference words: 5.00


XL5: Static and Dynamic Default Buttons

Article ID: Q105552

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0 custom dialog boxes, the two types of

default buttons are: static and dynamic.

MORE INFORMATION

================

In custom dialog boxes in Microsoft Excel, you can set one button in a

dialog box as the Default button. To set a button as the Default

button:

1. Select the button that you want to be the Default button.

2. From the Format menu, choose Object.

3. Select the Control tab.

4. Select the Default check box.

5. Choose OK to accept the change.

The Default button is also called the static default button. Pressing

the ENTER key will activate the Default button only if no other button

has the focus. Other buttons in the dialog box are called dynamic

default buttons. Pressing the ENTER key will activate a dynamic

default button only if that button currently has the focus.

If you have a dialog box that contains multiple controls, such as

those listed here, pressing ENTER when a control has the focus will

either activate the Default button or it will activate another button.

    If this control has the Focus   Pressing ENTER Activates this control

    ---------------------------------------------------------------------

    DefaultButton                   DefaultButton

    Button2                         Button2

    Button3                         Button3

    EditBox1                        DefaultButton

    CheckBox1                       DefaultButton

    CheckBox2                       DefaultButton

    ListBox1                        DefaultButton

    DropDown1                       DefaultButton

If you want a specific control to have the focus when you display a

dialog box, you can set the Tab Order of the dialog box so that the

control is at the top of the Tab Order. To access the Tab Order dialog

box:

1. From the Tools menu, choose Tab Order.

2. Select the desired control from the Tab Order list.

3. Choose the Move Up or Move Down button.

4. Choose the OK button.

REFERENCES

==========

"Visual Basic User's Guide," version 5.0, page 228-229

Additional reference words: 5.00


XL5: Can Use Same Name to Define Object and Defined Name

Article ID: Q105769

----------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

Microsoft Excel version 5.0 stores object names and defined names in

separate locations in memory. This method of storing names means that

a defined name and a named object on the same worksheet can have the

same name.

MORE INFORMATION

================

Microsoft Excel version 5.0 allows you to rename objects such as drawn

objects, Visual Basic controls, and bitmaps. To rename an object:

1. Select the object. (Some objects, such as buttons and Visual Basic

   controls, may require that you hold down the CTRL key while you

   select the object.)

2. In the name box, type the new name of the object and press ENTER.

   Note that naming the object in this way is NOT the same as naming

   it by using the Define Name dialog box.

   When the object is selected, you will see the object's new name in

   the name list.

To create an example of a named object and a named range that have the

same name, do the following:

1. On a new worksheet, use the Rectangle or Filled Rectangle drawing

   tool to draw a rectangle.

2. With the rectangle selected, type the name "Square" (without the

   quotation marks) in the name box and press ENTER.

3. In cell A1, type the word "Test" (without the quotation marks).

4. From the Insert menu, choose name, and then choose Define.

5. In the Names In Workbook box, type "Square" (without the quotation

   marks) and choose OK.

When cell A1 is selected, the name Square will appear in the name

box. Similarly, when you select the rectangle, Square will appear in

the name box.

Although the name box will display the name of an object when the

object is selected, it cannot be used to select an object. Using the

above example, if you select the name Square from the name box, the

cell named Square will be selected, not the Square object. You can

still select the object by clicking it with your mouse.

How to Refer to Named Objects and Defined Names in Visual Basic Code

--------------------------------------------------------------------

If you are writing a Visual Basic macro, you could use this code to

select the object Square:

   ActiveSheet.Rectangles("Square").Select

To select the range Square, you could use this code:

   Range("Square").Select

Because you are able to specify different objects ('Rectangles' versus

'Range'), you are able to select the proper items, even though they

have the same name.

REFERENCES

==========

"User's Guide," version 5.0, Chapters 10 and 13

Additional reference words: 5.00 define.name


XL5: Cannot Use Name Box to Define 3-D Name

Article ID: Q105770

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

Page 144 of the "User's Guide," version 5.0, states that you can

define a name with a 3-D reference. While this information is correct,

note that you cannot use the name box (on the Formula bar) to define

a 3-D name.

WORKAROUND

==========

To define a 3-D name, specify the 3-D reference in the Refers To box

in the Define Name dialog box (from the Insert menu, choose Name, and

choose Define).

MORE INFORMATION

================

To manually create a 3-D name

-----------------------------

The following example assumes you have a workbook that contains five

worksheets: Sheet1, Sheet2, Sheet3, Sheet4, and Sheet5, appearing in

that order.

To create a 3-D defined name "TestRange" that refers to the range

$A$1:$D$10 on sheets Sheet1 through Sheet4:

1. With a single worksheet tab selected, choose Define from the

   Insert menu, and choose Name.

2. In the Names In Workbook box, type "TestRange" (without the

   quotation marks).

3. In the Refers To box, type "=Sheet1:Sheet4!$A$1:$D$10" (without the

   quotation marks).

4. Choose OK.

To create a 3-D name with a Visual Basic procedure

--------------------------------------------------

Microsoft provides examples of Visual Basic procedures for

illustration only, without warranty either expressed or implied,

including but not limited to the implied warranties of merchantability

and/or fitness for a particular purpose. This Visual Basic procedure

is provided 'as is' and Microsoft does not guarantee that it can be

used in all situations. Microsoft does not support modifications of

this procedure to suit customer requirements for a particular purpose.

Note that a line that is preceded by an apostrophe introduces a

comment in the code--comments are provided to explain what the code is

doing at a particular point in the procedure. Note also that an

underscore character (_) indicates that code continues from one line

to the next. You can type lines that contain this character as one

logical line or you can divide the lines of code and include the line

continuation character. For more information about Visual Basic for

Applications programming style, see the "Programming Style in This

Manual" section in the "Document Conventions" section of the "Visual

Basic User's Guide."

To create the 3-D name "TestRange" in Visual Basic, you could use a

procedure similar to the following:

' Enter the following two lines of code as one single line and remove the

' underline character:

ActiveWorkbook.Names.Add Name:="TestRange", _

   RefersToR1C1:="=Sheet1:Sheet4!R1C1:R10C4"

REFERENCES

==========

"User's Guide," version 5.0, pages 132, 136, 144

Additional reference words: 5.00 define.name


XL5: Unexpected Results Editing Fields in PivotTable Wizard

Article ID: Q105846

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SYMPTOMS

========

In Microsoft Excel version 5.0, when you use the PivotTable Wizard,

you may not be able to add fields below the last field in the row,

column, or data area. When this occurs, the added field will push the

last displayed field out of view. Additionally, you may not be able to

edit all of your row, column, or data fields in the PivotTable Wizard.

CAUSE

=====

The PivotTable Wizard can only display up to 12 row fields, 9 column

fields, and 5 data fields; although you can add more fields to the

pivot table with the PivotTable Wizard, you cannot add them to the

bottom of the list of fields once the list reaches these limits. You

can only add fields above the last field displayed. When you do this,

the last field will be pushed out of view.

WORKAROUND

==========

Data Fields

-----------

To edit data fields not visible in the PivotTable Wizard, select the

desired data field in the pivot table and choose PivotTable Field from

the Data menu or a shortcut menu. To access the shortcut menu for a

data field, select one of the data fields in the pivot table and click

the right mouse button.

To move data fields not visible in the PivotTable Wizard, select the

cell containing the data field to move, and drag the data to the

desired location in the pivot table.

Row and Column Fields

---------------------

To edit row or column fields not visible in the PivotTable Wizard,

double-click the desired field button in the pivot table or select

the desired field button and choose PivotTable Field from the Data

menu.

To move row or column fields not visible in the PivotTable Wizard,

drag the field button to the desired location in the pivot table.

Steps to Reproduce Behavior

---------------------------

1. In a new worksheet, enter the following values:

      A1: a

      A2: 1

2. Select cells A1 and A2.

3. From the Insert menu, choose Name and choose Define. Type

   "database" (without the quotation marks) in the Names In Workbook

   field and choose OK.

4. From the Data menu, choose PivotTable.

5. In the Step 1 dialog box of the PivotTable Wizard, select Microsoft

   Excel List or Database and choose Next.

6. In the Step 2 dialog box, verify that Database appears as the Range

   and choose Next.

7. Drag the "a" field button to the Data area. (Repeat this step 6 times.)

The buttons are added to the Data area as Sum of a, Sum of a2, and so

on. The 6th data field (Sum of a6) cannot be added below the 5th data

field (Sum of a5). It can only be added above the 5th data field and

pushes the 5th data field out of view in the Data area.

REFERENCES

==========

"Users Guide," version 5.0, Chapter 4

Additional reference words: 5.00


XL5: RemoveItem and RemoveAllItems Methods May Not Work

Article ID: Q105877

----------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0, the RemoveItem and RemoveAllItems

methods cannot be used to remove items from a list box if the list box

is linked to a range on a worksheet or a Microsoft Excel 4.0 macro

sheet.

This is by design in Microsoft Excel version 5.0.

MORE INFORMATION

================

In Microsoft Excel 5.0, when you use a Visual Basic module to create a

list box, there are two ways you can add items to the list:

 - Use the AddItem method to add items to the list box.

   -or-

 - Do the following to link a worksheet or a Microsoft Excel 4.0 macro

   sheet range to the list box by setting an input range for the list

   box.

   1. In the dialog sheet, select the list box.

   2. From the Format menu, choose Object.

   3. Select the Control tab.

   4. In the Input Range edit box, enter the range where your list

      items are stored (for example, Sheet1!$A$1:$A$10).

   5. Choose OK to accept the change.

If your list box is linked to a worksheet or a Microsoft Excel 4.0

macro sheet range, you cannot use the RemoveItem or RemoveAllItems

methods to remove items from the list. Attempting to do so will result

in the following error message:

    RemoveItem method of ListBox class failed

Visual Basic Code Example

-------------------------

Microsoft provides examples of Visual Basic procedures for

illustration only, without warranty either expressed or implied,

including but not limited to the implied warranties of merchantability

and/or fitness for a particular purpose. This Visual Basic procedure

is provided 'as is' and Microsoft does not guarantee that it can be

used in all situations. Microsoft does not support modifications of

this procedure to suit customer requirements for a particular purpose.

Note that a line that is preceded by an apostrophe introduces a

comment in the code--comments are provided to explain what the code is

doing at a particular point in the procedure. Note also that an

underscore character (_) indicates that code continues from one line

to the next. You can type lines that contain this character as one

logical line or you can divide the lines of code and include the line

continuation character. For more information about Visual Basic for

Applications programming style, see the "Programming Style in This

Manual" section in the "Document Conventions" section of the "Visual

Basic User's Guide."

This Visual Basic code example displays a dialog box. When you select

one button, it hides the dialog box, removes the first item from the

list in the dialog box, and redisplays the dialog box. Another button

ends the procedure.

You need to use this example ONLY if your list box is linked to cells on

a worksheet or a Microsoft Excel 4.0 macro sheet: lists created using

AddItem can use the RemoveItem and RemoveAllItems methods to remove

items from a list.

This example assumes that you have a dialog sheet (Dialog1) and a

worksheet (Sheet1) both contained in the same workbook. The dialog

sheet contains two buttons, DoneButton and RemoveButton, and a list

box, List Box 1. The worksheet contains a list of items in cells

$A$1:$A$10. The list box has its Input Range set to Sheet1!$A$1:$A$10.

To run the example, position the cursor in line the line which reads

"Sub MainMacro()" and either press the F5 key or choose Start from the

Run menu.

'-----------------------------------------------------------

Public DoneFlag As Integer

Sub MainMacro()

   DoneFlag = 0                          'initialize DoneFlag

'  While the DoneFlag does not equal 1 (which will only occur if the

'  DoneButton is clicked), continue to loop through the Subroutine.

   Do

      DialogSheets("Dialog1").Show       'display the dialog box

   Loop Until DoneFlag = 1               'loop until DoneButton clicked

End Sub

Sub RemoveButton_Click

   DoneFlag = 0                          'ensure DoneFlag set to 0

   DialogSheets("Dialog1").Hide          'hide the dialog box

'  The following line deletes cell A1 ["Cells(1, 1)"] from worksheet

'  Sheet1.

   Worksheets("Sheet1").Cells(1, 1).Delete

End Sub

Sub DoneButton_Click

   DoneFlag = 1                          'set the DoneFlag

End Sub

'-----------------------------------------------------------

When either of the DoneButton or RemoveButton buttons are activated,

the appropriate subroutines (DoneButton_Click or RemoveButton_Click)

are run: in the RemoveButton subroutine, an item is deleted from the

list, which is automatically updated, and in the DoneButton

subroutine, a flag which indicates that you want to end the macro is

set.

The MainMacro subroutine then resumes and proceeds to either loop back

upon itself if DoneFlag equals 0 (if the RemoveButton was activated)

or exit the loop and end the macro (if the DoneButton was activated).

Note that this example uses the Delete method to eliminate a cell from

the list. If you want to redefine the list without deleting the cell,

you will need to use the ListFillRange property to determine the

proper range to use for your list.

Note also that when you use this method to remove items from a list,

you must hide and reshow the dialog box in order for the list to

appear correctly on the screen. This is accomplished by using the

DialogSheets("Dialog1").Hide command in the RemoveButton_Click

subroutine.

Additional reference words: 5.00


XL5: Linked References Not Updated in Closed Worksheet

Article ID: Q105944

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SYMPTOMS

========

In Microsoft Excel version 5.0, if you move cells in a source

worksheet, the original linked cell values in the dependent worksheet

may no longer be referenced.

CAUSE

=====

When you move linked cells in a source worksheet when the dependent

worksheet is closed, the references in the dependent worksheet will

not be adjusted.

WORKAROUND

==========

To work around this situation, do either of the following:

 - Make sure the dependent worksheet is open when you move linked

   cells in the source worksheet.

   -or-

 - Use named references when you link cells between worksheets. For

   example, use the following formula

      =[book1]SHEET1!test

   where test is the name defining cell A1 in SHEET1.

MORE INFORMATION

================

In Microsoft Excel versions earlier than 5.0, the links between worksheets

are adjusted when you move cells in the source worksheet. This behavior

occurs both when the dependent worksheet is open and when it is closed.

However, Microsoft Excel 5.0 tracks cell movement and adjusts references to

external documents only when the dependent worksheet is open. If the

dependent worksheet is closed and cells are moved in the source worksheet,

the changes are not reflected in the dependent worksheet when it is opened.

With the behavior in Microsoft Excel version 5.0, worksheets that contain

links take less room on your hard disk and are saved more quickly than

linked worksheets in earlier versions of Microsoft Excel.

Steps to Reproduce Behavior

---------------------------

 1. In cell A1 in SHEET1 of a new workbook, enter the following data:

       source

 2. From the File menu, choose Save. In the File Name box, type

    "SOURCE.XLS" (without the quotation marks) and choose OK.

 3. Select cell A1 and choose Copy from the Edit menu.

 4. In a new worksheet in a new workbook, select cell A1.

 5. From the Edit menu, choose Paste Special and choose Paste Link.

    The resulting formula is:

      =[SOURCE.XLS]SHEET1!$A$1

 6. From the File menu, choose Save. In the File Name box, type

    "DEPEND.XLS" (without the quotation marks) and choose OK.

 7. From the File menu, choose Close.

 8. Select cell A1 in SHEET1 of SOURCE.XLS and choose Cut from the

    Edit menu.

 9. Select cell D2 and choose Paste from the Edit menu.

10. From the File menu, choose Open. From the list of files, select

    DEPEND.XLS and choose OK.

The formula in A1 did not change references when the linked cell was

moved in SOURCE.XLS, and the value in cell A1 is now 0.

REFERENCES

==========

"User's Guide," version 5.0, pages 161-170

For more information about creating links between workbooks, choose

the Search button in Help and type:

    links, workbook, creating

Additional reference words: 5.00


XL5: Macro Cannot Scroll Chart Windows

Article ID: Q106004

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel, version 5.0, you have the ability to scroll a

chart window either vertically or horizontally. However, you cannot

scroll a chart window from a Visual Basic procedure or from a macro.

MORE INFORMATION

================

Microsoft Excel Version 5.0

---------------------------

In Visual Basic, you can use the SmallScroll and LargeScroll methods

to scroll a worksheet or a Microsoft Excel macro sheet window. Each

method accepts up to four arguments, indicating the number of rows or

columns to scroll down, up, right, and left.

Microsoft Excel Versions 4.0 and 5.0

------------------------------------

In a Microsoft Excel 4.0 macro, you can use any of six functions to

scroll a worksheet or a Microsoft Excel 4.0 macro sheet:

   This macro function   Does this

   ------------------------------------------------------------------

   VLINE                 Scrolls vertically through the active

                         window by the number of rows specified.

   VPAGE                 Scrolls vertically through the active window

                         by the number of windows specified ("window"

                         defined as the number of rows visible at any

                         given time).

   VSCROLL               Scrolls vertically through the active window

                         to the row specified.

The HLINE, HPAGE, and HSCROLL functions work similarly, but these

functions scroll the window horizontally instead of vertically.

If you attempt to use any of the above methods or functions when the

active window is a chart window, you will receive an error message.

This limitation does not affect other macro functions or methods

because items in charts do not need to be visible in order to be

affected by macros in Microsoft Excel.

Additional reference words: 5.00


XL5 Err Msg: Redefine Scenario Based on Current Cell Values?

Article ID: Q106006

----------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0, if you select a scenario from the

Scenarios box (located on the WorkGroup toolbar), and if that scenario

is already selected, you will receive the message

   Redefine 'Test' scenario based on current cell values?

where Test is the name of your scenario. A dialog box will appear

asking if you want to redefine the existing scenario.

This is by design.

MORE INFORMATION

================

If the Scenarios box lists the current scenario as Test, when you

reselect Test you will receive the error message above.

This does not occur when you use the Scenarios box to select a

scenario other than the current one. For example, if you select a

scenario called Sample while the current scenario is Test, you will be

able to use the Sample scenario without displaying any messages or

prompts.

If you have made changes to the worksheet and want to revert to the

current scenario as it was last saved, choose No when you are prompted

to redefine it. If you want to use the new values in your scenario,

choose Yes.

REFERENCES

==========

"User's Guide," version 5.0, page 554-555

Additional reference words: 5.00 manager


XL5: Open Subroutines Run Before Referenced Subroutines

Article ID: Q106008

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0, if you call a subroutine (for example,

TestSub) from another Visual Basic subroutine, Microsoft Excel will

search for the subroutine in the current workbook and then it will

search any libraries and/or workbooks to which the current workbook

has references (these references will be searched in top-to-bottom

order as they appear in the References dialog box).

Microsoft Excel will run the subroutine from the first location where

it is found. This is by design.

MORE INFORMATION

================

Steps to Reproduce Behavior

---------------------------

 1. In a new workbook, insert a new Visual Basic module (Module1).

 2. In the new module, enter the following subroutine:

      Sub TestSub()

         MsgBox "TestSub in ALPHA.XLS"

      End Sub

 3. Save the workbook as ALPHA.XLS.

 4. Repeat steps 1-3 two more times, but use the messages and workbook

    names BRAVO.XLS and CHARLIE.XLS, respectively. When you are done,

    you should have three workbooks (ALPHA.XLS, BRAVO.XLS, and

    CHARLIE.XLS), each of which contains a Module1.

 5. Quit and restart Microsoft Excel.

 6. Open ONLY the ALPHA.XLS workbook.

 7. In Module1, add the following subroutine:

      Sub Starter()

         TestSub

      End Sub

 8. From the Tools menu, choose References.

 9. In the References dialog box, choose Browse.

10. In the Browse dialog box, select the BRAVO.XLS file from the File

    Name list and choose OK.

11. In the References dialog box, choose Browse.

12. In the Browse dialog box, select CHARLIE.XLS from the

    File Name list and choose OK.

The Available References list box should now contain BRAVO.XLS and

CHARLIE.XLS at the bottom of the list.

13. Choose OK to exit the References dialog box.

14. In Module1, run the Starter subroutine.

A dialog box will appear containing the message "TestSub in ALPHA.XLS."

15. Delete the TestSub subroutine from Module1.

16. Run the Starter subroutine.

A message box will appear with the message "TestSub in BRAVO.XLS."

Microsoft Excel version 5.0, allows you to create references between

workbooks and other workbooks or libraries. If these workbook

references do not exist, a subroutine from a closed workbook cannot be

called. To run a subroutine located in a closed workbook, you can

create a reference between workbooks; this allows you to run the

subroutine by calling the subroutine's name.

For example, if you follow the steps in the "Steps to Reproduce

Behavior Section" above, you create references to both BRAVO.XLS and

CHARLIE.XLS. The first time the Starter subroutine is run (step 14),

Microsoft Excel finds the TestSub subroutine in ALPHA.XLS and runs it.

The second time the Starter subroutine is run (step 16), Microsoft

Excel does not find the TestSub subroutine in ALPHA.XLS (because it

has been deleted). It then checks the referenced libraries and modules

for the subroutine. The subroutine is first found in BRAVO.XLS, and so

Microsoft Excel runs the TestSub subroutine located there.

For more information on creating references and running subroutines

located in other workbooks, see the "Visual Basic User's Guide,"

Chapter 4.

Additional reference words: 5.00


XL5: PivotTable Wizard Does Not Automatically Refresh Data

Article ID: Q106243

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0, when you change the data or the field

name of a list that is included in a pivot table, and you then start

the PivotTable Wizard, the information in the existing PivotTable will

not be updated unless you choose the Refresh Data command from the

Data menu.

MORE INFORMATION

================

You can use the PivotTable Wizard to change the list range, the

arrangement of fields in the page, column, row, or data areas, and the

location of an existing pivot table. However, the PivotTable Wizard

does not automatically refresh the data in the existing pivot table.

To refresh the data, you must choose the Refresh Data command from the

Data menu.

This is by design.

Once you create a pivot table, you can select a cell in the pivot

table and choose the PivotTable command from the Data menu to display

the source information. However, choosing this command does not

automatically refresh the data.

NOTE: Changing the field names in the original list may cause

inaccurate results when the pivot table data is refreshed. To change

field names, choose the PivotTable Field command from the Data menu.

Steps to Reproduce Behavior

---------------------------

 1. In a new worksheet, type the following:

      A1: Name     B1: Age      C1: Dept

      A2: John     B2: 25       C2: 3

      A3: Jane     B3: 34       C3: 6

 2. From the Data menu, choose PivotTable.

 3. In the Step 1 Of 4 dialog box in the PivotTable Wizard, select

    the Microsoft Excel List Or Database option.

 4. In the Step 2 Of 4 dialog box in the PivotTable Wizard, select the

    range A1:C3.

 5. In the Step 3 Of 4 dialog box, move the Name field to the Row area

    and move the Age field to the Data area. Choose the Next button.

 6. In Step 4 Of 4, click cell E1 to place the PivotTable on this

    sheet, and then choose Finish.

 7. Change the value in cell B2 to 10.

 8. Select cell E2 (or any other cell in the pivot table).

 9. From the Data menu, choose PivotTable. In the PivotTable Wizard

    dialog box, choose the Finish button.

    Note that the table is not updated.

10. From the Data menu, choose Refresh Data.

    The PivotTable is updated.

11. Change the contents of cell B1 to "Bogus" (without the quotation

    marks). Then, select cell E2.

12. Repeat step 9.

    Note that the table does not update correctly.

13. Repeat step 10.

    Note that the pivot table does not update to reflect the new

    field name. It is still summing the field "Age" which no longer

    exists. To change a field name, re-create the pivot table or

    choose the PivotTable Field command from the Data menu.

REFERENCES

==========

"User's Guide," version 5.0, Chapter 25

For more information about customizing Pivot Tables, choose the

Search button in Help and type:

   pivot tables, customizing

Additional reference words: 5.00 database list


XL5: Print Area and Print Titles Boxes Empty in Print Preview

Article ID: Q106251

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0, if you are in print preview and you

choose the Page Setup button, the Print Area and Print Titles boxes on

Sheet tab of the Page Setup dialog box will be empty.

To verify the ranges for print area and print titles, you must exit

print preview and choose Page Setup from the File menu. The

definitions will then be visible in the Page Setup dialog box.

MORE INFORMATION

================

Steps to Reproduce Behavior

---------------------------

1. In a new worksheet, enter the following values:

   A1: Column A     B1: Column B     C1: AA     D1: BB

   A2: Column A     B2: Column B     C2: CC     D2: DD

   A3: Column A     B3: Column B     C3: EE     D3: FF

2. From the File menu, choose Page Setup.

3. Select the Sheet tab.

4. In the Print Area box, enter the range $C$1:$D$3.

5. In the Columns To Repeat At Left box, enter the range $A:$B, and

   choose OK.

7. From the File menu, choose Print Preview.

8. In print preview, choose the Setup button.

When you select the Sheet tab in the Page Setup dialog box, note that

the Print Area, Rows To Repeat At Top, and Columns To Repeat At Left

boxes are empty and you are not able to enter information in these

boxes.

REFERENCES

==========

"User's Guide," version 5.0, Chapter 14

Additional reference words: 5.00 5.0 Print_Area Print_Titles


XL5: Can't Use Varied Formats When Text Box Is Linked to Cell

Article ID: Q106338

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0, when you format text that is contained

in a text box that is linked to a worksheet cell, note the following

rules:

 - The text cannot be formatted in the text box itself. Instead, you

   must format the cell where the text originates.

   -and-

 - The text box will use only the formatting applied to the first

   character in the cell. Formatting applied to subsequent characters

   does not affect the text in the text box.

MORE INFORMATION

================

In Microsoft Excel version 5.0, you can create a link between a text

box and a cell in a worksheet. When you do this, the text in the cell

will also appear in the text box.

When you create a link this way, you cannot directly format the text

in the text box. Instead, you must format the text in the cell that

the text box is linked to. Because the text only uses the formatting

of the first character in the cell, it is not possible to apply

different formats to different parts of the text box when there is a

link.

When you create a text box that is not linked to a cell in a

worksheet, you can apply any desired formats to any characters in the

text box.

Steps to Reproduce Behavior

---------------------------

1. In cell A1 of a new worksheet, type the following:

      Alpha Bravo Charlie Delta

2. Click the Text Box tool and create a text box on the worksheet.

3. Select the text box and click the Formula bar.

4. In the Formula bar, type "=A1" (without the quotation marks)

   and press ENTER to create a link.

The text box will now contain the same text as the text that is in

cell A1.

5. Double-click cell A1 to edit the text directly in the cell.

6. Select the first A in Alpha.

7. From the Format menu, choose Cells.

8. Change the Font Size to 20 and select Bold Italic from the Font

   Style list box. Choose OK to accept the change.

9. Press ENTER.

In cell A1, only the capital A in "Alpha" will have changed. However,

all the text in the text box will change to 20-point bold italic text.

REFERENCES

==========

"User's Guide 1," version 5.0, Chapter 13, "Text Boxes"

Additional reference words: 5.00


XL5: Excel 4.0 Menus Option Setting Not Saved

Article ID: Q106357

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SYMPTOMS

========

When you switch to Microsoft Excel Version 4.0 menus, the setting is

not retained when you exit Microsoft Excel.

CAUSE

=====

In Microsoft Excel, you can switch to Microsoft Excel version 4.0

menus by choosing Options from the Tools menu and selecting the

Microsoft Excel Version 4.0 Menus option on the General tab. However,

when you exit Microsoft Excel, the Microsoft Excel Version 4.0 Menus

setting is not retained.

This behavior is by design.

The Microsoft Excel Version 4.0 Menus option is provided to help you

adjust to the 5.0 menus. However, when this option is selected, you do

not have full access to all the functionality of Microsoft Excel

version 5.0.

WORKAROUND

==========

The following sampe macro automatically switches to Microsoft Excel

Version 4.0 menus each time you start Microsoft Excel.

Microsoft provides examples of Visual Basic procedures for

illustration only, without warranty either expressed or implied,

including but not limited to the implied warranties of merchantability

and/or fitness for a particular purpose. This Visual Basic procedure

is provided 'as is' and Microsoft does not guarantee that it can be

used in all situations. Microsoft does not support modifications of

this procedure to suit customer requirements for a particular purpose.

Note that a line that is preceded by an apostrophe introduces a

comment in the code--comments are provided to explain what the code is

doing at a particular point in the procedure. Note also that an

underscore character (_) indicates that code continues from one line

to the next. You can type lines that contain this character as one

logical line or you can divide the lines of code and include the line

continuation character. For more information about Visual Basic for

Applications programming style, see the "Programming Style in This

Manual" section in the "Document Conventions" section of the "Visual

Basic User's Guide."

Example of Procedure

--------------------

To create the procedure, do the following:

1. In a new workbook, choose Macro from the Insert menu and choose Module.

2. In the new Module, enter the following code:

   ' Macro name must be auto_open() to run automatically

   Sub auto_open()

      ' Switch to Microsoft Excel version 4.0 menus

      Application.DisplayExcel4Menus = True

      ' Switch back to Sheet1 of default workbook

      Sheets("Sheet1").Select

   End Sub

4. From the File menu, choose Save.

5. In the File Name box, enter a name for the workbook.

6. In the Directories box, select the Excel Startup directory (XLSTART) and

   choose OK.

7. From the Window menu, choose Hide.

8. From the File menu, choose Exit. When asked to save the workbook, choose

   Yes.

The next time you start Microsoft Excel, this workbook will be opened

and the Microsoft Excel version 4.0 menus will be displayed.

REFERENCES

==========

"User's Guide," version 5.0, page 642

"Visual Basic User's Guide," version 5.0, page 266

For more information about Switching To The Version 4.0 Menus, choose

the Search button in Help and type:

    Microsoft Excel 4.0

Additional reference words: 5.00


XL5: Watch Pane Does Not Use Procedure Call Context

Article ID: Q106498

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SYMPTOMS

========

When you use the Debug window in the Microsoft Visual Basic

Programming System, Applications Edition, the Watch pane may not

display the watch expression value in the context of the selected

procedure call.

CAUSE

=====

When you run a procedure in the Immediate pane as part of the

debugging process for your macro, it will be added to the list of

procedure calls in the Calls dialog box. This functionality helps you

trace the operation of your code. However, if you select a procedure

in the Calls dialog box that is listed more than once and choose Show,

the current value of your watch expression does not change to the

context of the selected procedure.

For example, if your watch expression is a variable, and you run a

procedure in the Immediate pane multiple times, and you change the

value of the variable each time, the Calls dialog box cannot be used

to trace the different values of the variable. The value of the

variable in the Watch pane is the value at the last running of the

procedure, regardless of the instance of the procedure call selected

in the Calls dialog box.

Steps to Reproduce Behavior

---------------------------

1. Insert a new Visual Basic module in a workbook by choosing Macro

   from the Insert menu and choosing Module.

2. In the new module, type the following code:

      'Calls DoubleNum function

       Sub Initial()

          DoubleNum(2)

       End Sub

     'Multiply initial value by 2

      Function DoubleNum(Arg1 As Integer)

         DoubleNum = Arg1*2

         ' STOP value to bring up Debug window

         STOP

      End Function

3. From the Tools menu, choose Add Watch and in the Expression box,

   type "Arg1" (without the quotation marks).

4. In the Procedure box of the Add Watch dialog box, select DoubleNum

   and in the Module box, select the name of the module inserted in

   step 1. Under Watch Type, select the Watch Expression button and

   choose OK.

5. From the Run menu, choose Start. If the Macro dialog box appears,

   select Initial from the Macro Name/Reference list and choose Run.

6. When the Debug dialog box appears, select the Immediate tab and in

   the Immediate pane, type the following

      DoubleNum(5)

   and press Enter.

7. Click the button to the right of the Procedure box to display the

   Calls dialog box. From the Workbook.Module.Procedure list, select

   the first Book1.Module1.DoubleNum listed (there are two of these

   lines listed in the Calls dialog box) and choose Show.

8. From the Debug Window, select the Watch tab.

Note that the value displayed for Arg1 is 5, even though you selected

the first call to the DoubleNum function (which should have an Arg1 value

of 2) in the Calls dialog box.

NOTE: Microsoft Visual Basic for MS-DOS and Microsoft Visual Basic

for Windows have the same behavior.

REFERENCES

==========

"Visual Basic User's Guide," version 5.0, pages 153-169

For more information about Adding Watch Expressions and using a

Debugging Checklist, choose the Search button in Visual Basic Help and

type:

    debugging

Additional reference words: 5.00


XL5 Err Msg: "Must Exit Windows and Load SHARE.EXE. . ."

Article ID: Q106499

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SUMMARY

=======

In order to run Microsoft Excel for Windows, version 5.0, you must have the

Share program loaded on your computer. If you try to run Microsoft Excel

when Share is not loaded, you will receive the following error message:

   You must exit Windows and load SHARE.EXE in order to run Microsoft

   Excel.

To Correct This Problem

-----------------------

If you receive this error message, you can do either of the following:

 - Add the following line to your AUTOEXEC.BAT file

      <path>\share.exe /l:100 /f:5100

   where <path> is the drive and directory where the SHARE.EXE file is

   located (usually the MS-DOS directory). For example, if your MS-DOS

   directory is called DOS and is on drive C, add the following line

   to your AUTOEXEC.BAT file:

      c:\dos\share.exe /l:100 /f:5100

   You may need to add this line if it was removed from your

   AUTOEXEC.BAT file or if the Setup program was unable to add it.

   -or-

 - If you are running Microsoft Windows for Workgroups, try running

   in 386 enhanced mode.

MORE INFORMATION

================

The Share program provides the range locking functionality to

Microsoft Excel that is required in object linking and embedding (OLE)

version 2.0 operations.

If you are using Microsoft Excel version 5.0 under Windows version

3.1, or under Windows for Workgroups version 3.1 in standard mode, the

MS-DOS Share program must be run before you start Windows. If you are

using Windows for Workgroups version 3.1 or 3.11 in enhanced mode, an

equivalent of the Share program (VSHARE.386: see below) is automatically

loaded for you.

If the Microsoft Excel Setup program detects that Share (or an equivalent

such as VSHARE.386) is not loaded on your computer, the SHARE command is

added to your AUTOEXEC.BAT file.

You do not need to run the MS-DOS Share program if you run Microsoft

Excel under Windows for Workgroups 3.1 or 3.11 in 386 enhanced mode.

Windows for Workgroups uses a comparable file-sharing utility called

VSHARE.386.

Additional reference words: 5.00


XL5: Links Command Unavailable Unless Links Are External

Article ID: Q106676

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SUMMARY

=======

Links between worksheets that are located in the same workbook will

not appear in the Links dialog box. The Links dialog box will only

show external links (that is, links between different workbooks).

This is by design.

MORE INFORMATION

================

The purpose of the Links dialog box is to handle the pathing issues

that result when links are made between different files. Since sheets

in the same workbook are part of the same file, it is not necessary to

display them in the Links dialog box.

This behavior is new in Microsoft Excel version 5.0. In Microsoft

Excel versions 4.x, links between different sheets in the same

workbook would appear in the Links dialog box regardless of whether

the workbook was bound or not.

Steps to Reproduce Behavior

---------------------------

1. In cell A1 on Sheet1, type:

      =Sheet2!A1

Note that the the Links command on the Edit menu is unavailable

(dimmed).

3. Open a new workbook (for example, Book2).

4. Switch to the previous workbook.

5. In cell A2 type:

      =[Book2]Sheet1!A1

The Links command is now available on the Edit menu.

REFERENCES

==========

"Users Guide," version 5.0, Chapter 1

Additional reference words: 5.00 grey choose


XL5: Unexpected Results When Pasting Linked Text Box

Article ID: Q106718

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0, if you copy and paste a text box that

has been linked to a cell in a worksheet, the link may not be updated

properly, depending on where you paste the text box.

WORKAROUND

==========

If a text box is pasted into another sheet, you cannot automatically

maintain the link to the original sheet. To do this manually do the

following (in this exmple the text box is linked to Sheet1!$A$1):

1. Copy the text box.

2. Activate the sheet (for example, Sheet2) where the text box will be

   pasted.

3. Paste the text box in the desired location.

4. While the text box is still selected, edit the formula in the formula

   bar so that it reads "=Sheet1!$A$1" (without the quotation marks).

5. Press ENTER.

The text box is now linked to the cell to which it was originally linked.

MORE INFORMATION

================

If you copy a text box from a worksheet or a Microsoft Excel version 4.0

macro sheet (for example, Sheet1) that has been linked to a cell in the

same sheet with the formula =$A$1 or =Sheet1!$A$1, you will receive

different results depending on where you paste the text box.

Worksheet or Microsoft Excel 4.0 Macro Sheet

--------------------------------------------

The text box will become linked to cell A1 on the sheet where you

paste it. For example, if you paste the text box in Sheet2, the text

box will be linked to cell A1 in Sheet2.

Chart Sheet

-----------

The text box will lose its link to cell A1, but the value displayed in

the text box will remain. For example, if cell A1 in Sheet1 contains

the text "Sample," the text box will still say "Sample" when you paste

it onto the chart.

Dialog Sheet

------------

The text box will attempt to link to cell A1 in the dialog sheet.

Because there are no cells in a dialog sheet, the text box will appear

blank. In this case, use the workaround shown above to restore the proper

link.

REFERENCES

==========

"User's Guide 1," version 5.0, page 231

Additional reference words: 5.00


XL5: Can't Reference Global Name on First Sheet

Article ID: Q107077

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SYMPTOMS

========

On the first worksheet in a workbook in Microsoft Excel, you will not

be able to refer to a global name when there is a local name on the

first worksheet that is the same as the global name.

WORKAROUND

==========

To work around this situation, do one of the following:

 - Do not use duplicate global and local names in a workbook.

   -or-

 - When the first worksheet contains a duplicate local name, use the

   following formula to refer to a global name on that first

   worksheet

       =Sheet!GlobalName

   where "Sheet" is the name of any worksheet in the current workbook

   that does not contain a local name that is the same as

   "GlobalName."

   -or-

 - When the first worksheet contains a duplicate local name and every

   other worksheet in the workbook also contains a duplicate local

   name, do the following to refer to a global name on the first

   worksheet in the workbook:

   1. Enter the following global reference in a worksheet other than

      the first worksheet in the workbook:

         =Book!GlobalName

   2. Copy this reference and paste it in the first worksheet. The

      global name is referenced correctly.

MORE INFORMATION

================

Normally, to refer to a global name on a workbook, only the global

name or the workbook name and the global name are necessary. For

example, to refer to the global name "test" on any sheet in the

workbook "book1," use the following formula:

   book1!test

However, to refer to a global name on the first worksheet when the

first worksheet contains a duplicate local name, you must use the name

of another worksheet in the workbook along with the global name. For

example, to refer to the global name "test" on sheet1, when sheet1

contains the local name "sheet1!test," use the following formula

   sheet2!test

where "sheet2" is the name of any worksheet in the current workbook

that does not contain "test" as a local name. If you use "book1" or

"sheet1" in place of "sheet2" in the above example, the local name

"test" is referenced instead of the global name.

Steps to Reproduce Behavior

---------------------------

1. In the first worksheet of a new workbook, enter the following data:

      A1: global

      A2: local

2. Select cell A1, and from the Insert menu, choose Name and choose

   Define.

3. In the Names In Workbook box, type "test" (without the quotation

   marks) and choose OK.

4. Select cell A2, and from the Insert menu, choose Name, and choose

   Define.

5. In the Names In Workbook box, type "sheet1!test" (without the

   quotation marks) where "sheet1" is the name of the active

   worksheet, and choose OK.

6. In cell B1, enter the following formula

      =book1!test

   where "book1" is the name of the current workbook.

The value of the cell is "local" even though the formula entered

refers to the global name. Select cell B1 and note that the reference

changes to the following:

   =sheet1!test

This behavior only occurs in the first worksheet in the workbook.

REFERENCES

==========

"User's Guide," version 5.0, pages 144-150

For more information about Naming a Cell, Range or Formula, choose

the Search button in Help and type:

    naming

Additional reference words: 5.00


XL5: Alert Function Does Not Beep in Excel Version 5.0

Article ID: Q107137

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel versions earlier than 5.0, the ALERT() macro

function automatically causes the computer to beep. In Microsoft Excel

version 5.0, the computer does not automatically beep when you use the

ALERT() macro function or the Visual Basic MsgBox function.

MORE INFORMATION

================

Microsoft Excel version 5.0 does not automatically beep when an

ALERT() box or MsgBox is displayed. This allows you to determine what

sound (if any) you want to play when a message box is displayed.

If you do want to hear a beep sound, you must use the BEEP() function

or the Visual Basic Beep statement before you issue the ALERT()

function or the MsgBox command. The following examples illustrate how

to do this.

Visual Basic Example

--------------------

Microsoft provides examples of Visual Basic procedures for

illustration only, without warranty either expressed or implied,

including but not limited to the implied warranties of merchantability

and/or fitness for a particular purpose. This Visual Basic procedure

is provided 'as is' and Microsoft does not guarantee that it can be

used in all situations. Microsoft does not support modifications of

this procedure to suit customer requirements for a particular purpose.

Note that a line that is preceded by an apostrophe introduces a

comment in the code--comments are provided to explain what the code is

doing at a particular point in the procedure. Note also that an

underscore character (_) indicates that code continues from one line

to the next. You can type lines that contain this character as one

logical line or you can divide the lines of code and include the line

continuation character. For more information about Visual Basic for

Applications programming style, see the "Programming Style in This

Manual" section in the "Document Conventions" section of the "Visual

Basic User's Guide."

In Microsoft Excel 5.0, open a new Visual Basic module and type the

following code:

Sub Test()

   Beep                    'Causes the computer to beep

   MsgBox "Hello", 64      'Displays an information message box

End Sub

Microsoft Excel 4.0 Macro Example

---------------------------------

In Microsoft Excel 5.0, open a new Microsoft Excel 4.0 macro sheet and

type the following macro:

   A1: =BEEP()

   A2: =ALERT("Hello")

   A3: =RETURN()

Explanation of Macro Code

-------------------------

A1: Causes the computer to beep.

A2: Displays an information alert box with the text: "Hello"

A3: Ends the macro.

Instead of using the =BEEP() function or the Beep procedure, you can

use the SOUND.PLAY() function to play any sound before displaying the

message box. For example, for a Visual Basic macro, use the following

code:

   Application.ExecuteExcel4Macro "SOUND.PLAY(,""C:\WINDOWS\CHIMES.WAV"")"

For a Microsoft Excel version 4.0 macro, use the following code:

   =SOUND.PLAY(,"C:\WINDOWS\CHIMES.WAV")

Additional reference words: 5.00 dialog


XL5: Using Global and Local References in Formulas

Article ID: Q107197

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel, you can define names on a worksheet as either

global or local names.

Global Names

------------

Global names are available to the entire workbook. For example, if

you define cells A1:A10 on Sheet1 as Sales, you can sum the sales

on Sheet2 with the formula =SUM(Sales). When you use a global name,

you do not need to indicate a sheet name because a global name is

available to any sheet in the workbook.

Local Names

-----------

A local name is available only on the sheet where it is defined. You

create a local name by including the sheet name with an exclamation

point (!) before the name in the Define Name dialog box. For example,

in the Name box, type "Sheet1!Sales" (without the quotation marks) and

in the Refers To box, type "Sheet1!$A$1:$A$10" (without the quotation

marks). To use this name on a another sheet in the workbook, create an

external reference, for example, type:

   =SUM(Sheet1!Sales)

Local names take precedence over global names. To refer to a global

name that duplicates a local name on the active sheet, use an

external reference by specifying the workbook name.

The following discusses the ways to refer to global and local names,

both on the current workbook and on another workbook.

MORE INFORMATION

================

Referring to Global and Local Names Located on the Current Workbook

-------------------------------------------------------------------

The following table uses as an example a workbook called "Products"

which contains the worksheets "Sheet1," "Sheet2," and "Sheet3." Sales

is a global name on Products and is also a local name defined on both

Sheet1 and Sheet2 but not on Sheet3. Sheet1!Sales is the name of the

local Sales on Sheet1. Sheet2!Sales is the name of the local Sales on

Sheet2. Sales is the name of the global Sales on Products.

   To Refer to this name     On this sheet     Use this formula

   ------------------------------------------------------------

   Sheet1!Sales              Sheet1            =Sales

   Sheet1!Sales              Sheet2            =Sheet1!Sales

   Sheet1!Sales              Sheet3            =Sheet1!Sales

   Sheet2!Sales              Sheet1            =Sheet2!Sales

   Sheet2!Sales              Sheet2            =Sales

   Sheet2!Sales              Sheet3            =Sheet2!Sales

   Sales                     Sheet1            =Sheet3!Sales (see

                                                NOTE below)

   Sales                     Sheet2            =Products!Sales

   Sales                     Sheet3            =Sales

NOTE: To refer to the global name Sales from Sheet1 which also

contains the local name Sales, you must use the name of a worksheet

in the workbook that does not contain the local name Sales. In this

case, Sheet3 must be used in the reference to the global name

Sales. For more information on this exception involving the first

worksheet in a workbook, query on the following words in the Microsoft

Knowledge Base:

   reference and global and first

Search Order of Global and Local Names on the Current Workbook

--------------------------------------------------------------

If you enter the formula =Sales on a worksheet in Products, the

following search order is used:

   Local Sales on the current worksheet in Products

   Global Sales on Products

If you enter the formula =Sheet1!Sales or the formula

=[Products]Sheet1!Sales on a worksheet in Products, the following

search order is used:

   Local Sales on Sheet1

   Global Sales on Products

If you enter the formula =Products!Sales on a worksheet in

Products, the formula will look for the following defined name:

   Global Sales on the Products workbook (see NOTE above)

Referring to Global and Local Names Located on Another Workbook

---------------------------------------------------------------

The following table uses Workbook2, as well as the Products workbook

defined above.

                             On a worksheet

   To Refer to this name     in this workbook      Use this formula

   -----------------------------------------------------------------

   Sheet1!Sales              Workbook2             =[Products]Sheet1!Sales

   Sheet2!Sales              Workbook2             =[Products]Sheet2!Sales

   Sales                     Workbook2             =Products!Sales

To refer to a global or local name on another workbook, you must

include the workbook name.

Search Order of Global and Local Names on Another Workbook

----------------------------------------------------------

If you enter the formula =[Products]Sheet1!Sales on a worksheet in

Workbook2, the following search order is used:

   Local Sales on Sheet1

   Global Sales on Products

If you enter the formula =Products!Sales on a worksheet in

Workbook2, the formula will look for the following defined name:

   Global Sales on the Products workbook

If the name Sales is found in the first step of the search, that

value of Sales is returned, and the search is complete. If the name

Sales is not found after the final step in the search, the #NAME!

error will be returned.

REFERENCES

==========

"User's Guide," version 5.0, pages 144-150

For more information about Naming a Cell, Range or Formula, choose

the Search button in Help and type:

    naming

Additional reference words: 5.00


XL5: Using "Most Recently Used" Category in Function Wizard

Article ID: Q107221

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

In the Microsoft Excel Function Wizard, the Most Recently Used

function category contains the last 10 functions you accessed in the

Function Wizard. When you access a function from the Function Wizard,

that function is added to the Most Recently Used list. If there are

already 10 functions in the list, the last function on this list is

removed from the list and the new function is added.

It is not possible to remove the Most Recently Used list from the

Function Wizard.

When you start the Function Wizard, the most recently used function

category is automatically selected. If the last category you used was

the Most Recently Used category, this category will be selected when

you next use the Function Wizard.

MORE INFORMATION

================

Resetting the Most Recently Used List

-------------------------------------

Although you cannot totally clear the Most Recently Used function

list, you can reset it. Usually there are 10 functions in the Most

Recently Used list; however, the first time you start Microsoft Excel

or the first time you start Microsoft Excel after you reset the list,

the list contains 9 functions.

To reset the Most Recently Used list, do the following to edit the

EXCEL5.INI file (this file should be located in your WINDOWS

directory):

1. Quit Microsoft Excel.

2. Open the EXCEL5.INI file using a text editor, such as Notepad or

   the Edit program included with MS-DOS 5.x or 6.x.

3. In the [Microsoft Excel] section of the EXCEL5.INI, change the line

   that begins MRUFuncs=. Delete all characters following the equal

   sign so that the line reads:

      MRUFuncs=

4. Save the EXCEL5.INI file and exit the text editor.

When you restart Microsoft Excel, the Most Recently Used list will

contain the following 9 functions: AVERAGE(), COUNT(), IF(), MAX(),

MIN(), PMT(), ROUND(), SUM(), SUMIF(), and VLOOKUP(). As you use

different functions, these default functions will be replaced with the

functions you use.

Preventing the Most Recently Used Function List from Being Updated

------------------------------------------------------------------

The only way to prevent the Most Recently Used Function list from

being updated is to not use the Function Wizard. Functions that are

entered without using the Function Wizard are not added to the Most

Recently Used Function list.

Additional reference words: 5.00


XL5: Copy Picture Does Not Copy Row or Column Headings

Article ID: Q107222

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

In Microsoft Excel version 5.0, when you use the Copy Picture command

to copy cells on a worksheet, row and column headings are not copied.

In earlier versions of Microsoft Excel, Copy Picture includes row and

column headings.

To copy a picture of cells such that the picture includes the row and

column headings, do either of the following:

 - Press ALT+PRINT SCREEN to take a picture of the entire Microsoft

   Excel window. The picture can then be pasted into a graphics

   program, such as Windows Paintbrush, for editing.

   -or-

1. From the File menu, choose Page Setup.

2. Select the Sheet tab.

3. Select the Row and Column Headings check box and choose OK to

   accept the change.

4. Select the range of cells you want to copy.

5. Press and hold down the SHIFT key, and choose Copy Picture from the

   Edit menu.

6. Select the As Shown When Printed option and then choose OK to

   accept the change.

7. Paste the image in the desired location.

The pasted image will include row and column headings, but it will

appear in black and white unless your printer supports color printing.

If you have a color printer, the pasted image will appear in color.

Additional reference words: 5.00


XL5 Err Msg: Error in Procedure Causes "Cannot Find Procedure"

Article ID: Q107469

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SYMPTOMS

========

In Microsoft Excel, when you open a workbook that contains a defined-name

automatic Visual Basic procedure, you may receive the following error

message

   Cannot find <procedure>, which has been assigned to run each time

   [<workbook>]<worksheet> is opened. Continuing could cause errors. Cancel

   opening [<workbook>]<worksheet>?

where <procedure> is the name of the Visual Basic procedure that has

been defined on a worksheet in <workbook> with a name that begins

with Auto_Open and <worksheet> is the first worksheet in <workbook>.

CAUSE

=====

This error message may be caused by one of the following:

 - The procedure that is being called cannot be located, as the error

   message states.

   -or-

 - Although Microsoft Excel does locate the procedure, the procedure

   contains an error, such as a syntax error.

RESOLUTION

==========

If you receive this error message, and you have verified that the

procedure does exist in the location specified in the defined name,

do the following to view the error in the procedure that is causing

the incorrect error message:

1. In the worksheet that the procedure is defined on, choose Macro from the

   Tools menu.

2. From the Macro Name/Reference list, select the procedure that is causing

   the error, and choose Run.

The real error message appears, making it easier for you to debug the

procedure. When you correct the problem with the macro, the "Cannot

find..." error message will no longer appear.

MORE INFORMATION

================

You can create a defined name on a worksheet that refers to a Visual Basic

procedure so that it automatically runs that procedure when the worksheet

is opened, closed, activated, or deactivated. If you define a name that

begins with Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate, and

the name refers to a Visual Basic procedure, that procedure will run when

the described action is performed on the worksheet.

If the procedure contains an error, such as a syntax error, the error

message described above will occur when the procedure is run as a defined-

name automatic procedure. The error indicates that the procedure was not

found, although the actual cause of the error may be a runtime error in the

procedure itself.

Steps to Reproduce Behavior

---------------------------

1. In new workbook, choose Macro from the Insert menu, and choose Module.

2. In the module, type the following:

      Sub Run_Error()

         Error

      End Sub

3. Choose the Sheet1 tab, and from the Insert menu, choose Name, and choose

   Define.

4. In the Names In Workbook box, type "Sheet1!Auto_Open_Test" (without the

   quotation marks), and in the Refers To box, type "=Run_Error" (without

   the quotation marks), and choose OK.

5. From the File menu, choose Close, and choose Yes to save the workbook.

6. In the File Name box, type AUTO.XLS, and choose OK.

7. From the File menu, choose Open. From the File Name list, choose

   AUTO.XLS and choose OK.

Even though Run_Error does exist in AUTO.XLS, you will receive the error

message:

   Cannot find Run_Error which has been assigned to run each time

   [AUTO.XLS]Sheet1 is opened...

If you run the macro by choosing Macro from the Tools menu, selecting

Run_Error and choosing OK, the correct error message, "Syntax Error",

appears.

NOTE: If the same macro is run as an automatic procedure (the

procedure is given the name Auto_Open or Auto_Close), the correct

error will appear. For more information about creating automatic

procedures and using defined-name automatic procedures, see pages

266-268 in the "Visual Basic User's Guide," version 5.0.

Additional reference words: 5.00


XL5: Not All Worksheet Functions Supported As Application

Article ID: Q107564

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SUMMARY

=======

In the Microsoft Visual Basic Programming System, Applications

Edition, not all Microsoft Excel worksheet functions are supported as

methods of the Application object.

MORE INFORMATION

================

You can write Visual Basic procedures that call Microsoft Excel

worksheet functions. The worksheet functions that are available to

Visual Basic procedures are in the Application object, and are listed

in the Methods/Properties list for the Application object in the

Visual Basic Object Browser.

Most worksheet functions that are not available as methods of the

Application object have an equivalent Microsoft Visual Basic built-in

operator or function. For example, the Mod() worksheet function is

not available as a method of the Application object because there is

an equivalent Mod operator built-in to Visual Basic. This is by

design, because a Visual Basic operator works faster than a Microsoft

Excel function in a Visual Basic module.

To display a list of all the Microsoft Excel worksheet functions

available as methods of the Application object in Visual Basic, do

the following:

1. Choose the Search button in Visual Basic Reference Help, type "worksheet

   functions" (without the quotation marks), and choose Show Topics.

2. From the list of topics, select Using Worksheet Functions In Visual

   Basic, and choose Go To.

To locate the equivalent Visual Basic operator or function of a Microsoft

Excel worksheet function that is not available as an Application object

method, do the following:

1. Choose the Search button in Visual Basic Online Help and type:

      programming language

2. Choose Show Topics and choose Go To.

3. Select the operator or function with the same name as the function you

   want to use.

The following is a list of worksheet functions that are not available

as methods of the Application object and do not have an equivalent

Visual Basic operator or function with the same name:

Microsoft Excel                   Visual Basic

worksheet function                equivalent

------------------------------------------------------------

CHAR()                            CHR function

CODE()                            ASC function

DATE()                            DATESERIAL function

ERROR.TYPE()                      ERR function

EXACT()                           STRCOMP function

INDIRECT()                        No direct equivalent - use Range, Cells,

                                  Offset, or any other way of

                                  referencing cells.

INFO()                            No direct equivalent - use the following:

                                  CURDIR function, the following

                                  Application properties (MEMORYTOTAL,

                                  MEMORYUSED, MEMORYFREE, VERSION,

                                  OPERATINGSYSTEM, CALCULATION, COUNT)

ISBLANK()                         ISEMPTY function

ISREF()                           TYPENAME function

LOWER()                           LCASE function

N()                               No equivalent (none needed)

NA()                              No equivalent - use

                                  ActiveCell.Value = "#N/A"

RAND()                            RND function

SIGN()                            SGN function

SQRT()                            SQR function

T()                               No equivalant (none needed)

TODAY()                           DATE function

TRUNC()                           FIX function

TYPE()                            TYPENAME function

UPPER()                           UCASE function

VALUE()                           VAL function

NOTE: The Microsoft Excel worksheet function and the Visual Basic

equivalent operator or function are not always calculated the same

way and may give different results, even when they have the same

name.

Using the Visual Basic equivalent operator or function is preferred

for increased speed and flexibility in calculations. Additionally,

the Visual Basic operator or function is available to all

applications that use the Microsoft Visual Basic Programming System,

Applications Edition.

Additional reference words: 5.00


XL5: Cannot Add Sheet After Last Sheet in a Single Action

Article ID: Q107622

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0, it is not possible to add a new

sheet after the last sheet in the workbook in a single action. For

example, if you have a workbook that contains Sheet1 and Sheet2 (in

left-to-right sheet tab order), you cannot add a new sheet to the

right of Sheet2.

This situation occurs regardless of whether you add the new sheet manually,

use Visual Basic code, or use Microsoft Excel 4.0 macro code.

You can add a new sheet to a workbook and make the sheet become the last

sheet in the workbook, but at least two separate actions are required to do

this.

MORE INFORMATION

================

If your application requires that a new sheet be added or inserted

after the last sheet in the workbook, use either of the following

procedures:

 - If you manually add the new sheet to the workbook (by choosing the

   appropriate item in the Insert menu, for example), you can drag the new

   sheet's tab to the right of all of the other sheet tabs. This makes the

   new sheet also become the last sheet in the workbook.

   -or-

 - If you are using a Visual Basic subroutine to add the new sheet to the

   workbook, you can use the Move method to move the new sheet to the

   proper location. An example of how to do this is shown below.

Visual Basic Code Example

-------------------------

Microsoft provides examples of Visual Basic procedures for

illustration only, without warranty either expressed or implied,

including but not limited to the implied warranties of merchantability

and/or fitness for a particular purpose. This Visual Basic procedure

is provided 'as is' and Microsoft does not guarantee that it can be

used in all situations. Microsoft does not support modifications of

this procedure to suit customer requirements for a particular purpose.

Note that a line that is preceded by an apostrophe introduces a

comment in the code--comments are provided to explain what the code is

doing at a particular point in the procedure. Note also that an

underscore character (_) indicates that code continues from one line

to the next. You can type lines that contain this character as one

logical line or you can divide the lines of code and include the line

continuation character. For more information about Visual Basic for

Applications programming style, see the "Programming Style in This

Manual" section in the "Document Conventions" section of the "Visual

Basic User's Guide."

The following Visual Basic code example inserts a new sheet into a workbook

and makes the new sheet become the last sheet in the workbook by moving it

to the right of the rightmost sheet.

This example assumes you have a workbook that contains at least one

Visual Basic module and any number of other sheets.

To run the example, position the cursor in the line that reads

"Sub AddNewSheet()" and either press the F5 key or choose Start from

the Run menu.

'----------------------------------------------------------------------

Sub AddNewSheet()

   Application.ScreenUpdating = False    'prevents screen refreshing

   'This line adds a new worksheet to the workbook. Alternatively, use:

   '

   'DialogSheets.Add                    to add a dialog sheet

   'Charts.Add                          to add a chart sheet

   'Modules.Add                         to add a Visual Basic module

   'Sheets.Add Type:=xlExcel4MacroSheet to add a MS Excel 4.0 Macro sheet

   Worksheets.Add

   'This line makes the new sheet (which is also the active sheet) the

   'last sheet in the workbook.

   ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)

   Application.ScreenUpdating = True     'reenables screen refreshing

End Sub

'----------------------------------------------------------------------

When you run the AddNewSheet subroutine, it will add a new sheet to

the active workbook and make the new sheet become the last sheet in

the workbook.

If you try to add a new sheet after the last sheet in the workbook,

all in a single action, you will receive the error message:

   Add method of Sheet class failed

For example, the following command will return an error message

   Worksheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count)

because it attempts to add a new sheet after the last sheet in the

workbook.

Additional reference words: 5.00


XL5: Cannot Use Crosstab Add-In Macro in Worksheet Object

Article ID: Q107705

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SYMPTOMS

========

In Microsoft Excel, you cannot create a crosstab table in an embedded

Microsoft Excel 5.0 worksheet object. The Crosstab ReportWizard

appears to go through all the steps to create a crosstab table, but

the table is not created and you do not receive any message informing

you that the table cannot be created.

CAUSE

=====

When you create a crosstab table, the crosstab add-in macro creates a

new workbook to store temporary data. Because the creation of a new

workbook is not allowed when you are in an embedded worksheet object,

the crosstab table creation fails.

WORKAROUND

==========

To work around this situation, do either of the following:

 - Create a pivot table to display your data in the embedded Microsoft

   Excel 5.0 worksheet object. A pivot table is created successfully in an

   embedded worksheet, and the pivot table feature in Microsoft Excel

   version 5.0 is easier to use than the crosstab table feature when you

   are working with large amounts of data.

   -or-

 - If you need to use the crosstab feature in a Microsoft Excel 5.0

   worksheet object, use the following steps:

   1. Create the crosstab table in a new worksheet in Microsoft Excel

      version 5.0.

   2. Save the worksheet.

   3. Insert the saved worksheet as an object in the server application. To

      do this, in the Object dialog box, select the Create From File tab

      rather than the Create New tab.

Steps to Reproduce Problem

--------------------------

NOTE: The following steps assume you already have the crosstab

feature set up and working correctly in Microsoft Excel version 5.0.

1. Start Microsoft Word for Windows version 6.0.

2. From the Insert menu, choose Object, and Select the Create New tab. From

   the Object Type list, select Microsoft Excel 5.0 Worksheet and choose

   OK.

3. In the embedded worksheet, enter the following data:

      A1: a

      A2: 1

4. Select the range A1:A2 and from the Insert menu, choose Name, and choose

   Define. In the Names In Workbook box, type "database" (without the

   quotation marks).

5. Do the following to create the crosstab table:

   a. From the Format menu (the Data menu if you are using Microsoft Excel

      4.0 menus), choose Crosstab to run the Crosstab ReportWizard.

   b. In the Introduction screen, choose Create New Crosstab.

   c. In the Row Categories screen, from the Fields In Database list,

      choose a, and choose Add. Choose Next.

   d. In the Column Categories screen, choose Next.

   e. In the Value Categories screen, from the Fields In Database list,

      choose a, and choose Add. Choose Next.

   f. In the Final screen, choose Create It.

Control is returned to the embedded worksheet, but the crosstab table is

not created. You do not receive an error message alerting you as to why the

crosstab table is not created.

For more information about using the Crosstab command in Microsoft

Excel version 5.0, query on the following words in the Microsoft

Knowledge Base:

   crosstab and pivot and table

Additional reference words: 5.00


XL5: Chart Number Format Not Updated If Data Source External

Article ID: Q107752

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SYMPTOMS

========

When you change the number format of the source data for a Microsoft Excel

chart, the number format of the data in the chart is not updated if the

following conditions are true:

    - The source data is in another workbook

      -and-

    - The chart sheet is not the active sheet in its workbook

CAUSE

=====

When you create a chart in one workbook that contains data from another

workbook, the format of the chart data will be updated only when the chart

sheet is active and the workbook that contains the source data is open.

WORKAROUND

==========

To make sure that your chart data formats are updated when you change

the format of the source data, do either of the following:

 - Keep the source data worksheet and the chart in the same workbook.

   -or-

 - Activate the chart window before you change the format of the source

   data.

Steps to Reproduce Behavior

---------------------------

1. In a new workbook that contains at least two worksheets, in one of the

   worksheets, enter the following data:

      A1: 1

2. Select cell A1 and press F11 to create a chart that is a new sheet in

   the workbook.

3. Do the following to drag the sheet on which you entered the data to a

   new workbook:

   a. From the File menu, choose New to create a new workbook (Book2 in

      this example).

   b. From the Window menu, choose Arrange, and under Arrange, select the

      Tiled option.

   c. Select the tab of the worksheet with the chart data and drag it to

      the Book2 window.

   d. Save the new workbook as BOOK2.XLS.

4. In the first workbook, select the tab of another worksheet so that the

   chart sheet is not the active sheet.

5. In the worksheet that you moved to BOOK2.XLS, select cell A1, and from

   the Format menu, choose Cells. Select the Number tab, and from the

   Category list, choose Percentage. From the Format Codes list, choose 0%,

   and choose OK.

6. In BOOK2.XLS, from the File menu, choose Close, and choose Yes to save

   the changes.

7. In the first workbook, select the chart sheet tab.

The numbers on the chart are still formatted with the General format and do

not reflect the percentage format that you applied to the linked data in

BOOK2.XLS.

NOTE: If the chart sheet is active in the first workbook at any time while

BOOK2.XLS is open, the values on the chart have the same formatting as the

linked data.

REFERENCES

==========

For more information about Creating A Chart Sheet In A Workbook, choose the

Search button in Help and type:

    creating, chart sheets

Additional reference words: 5.00


XL5: Scatter Chart Line Disappears After Changing Series Axis

Article ID: Q107753

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

SYMPTOMS

========

In Microsoft Excel, when you edit a scatter chart that has been formatted

with the ChartWizard or an applied autoformat and if a series in the chart

is formatted with a line pattern, when you change the axis that the series

is plotted on, the line for that series may disappear.

WORKAROUND

==========

When you format a scatter chart with the ChartWizard or with an autoformat,

to change the axis that the series is plotted on while still retaining your

series format, do the following:

1. From the Format Data Series dialog box, choose the Patterns tab. Under

   Line, reselect Automatic (even though it is already selected).

2. Select the Axis tab, and under Plot Series On, select the axis that is

   not already selected.

3. Choose OK to close the Format Data Series dialog box.

NOTE: Steps 1 and 2 above can be done in any order, but must be done

before step 3.

MORE INFORMATION

================

You can plot series on a scatter chart on either the primary axis or the

secondary axis. You can format the series with a combination of line and

marker options. If you use the ChartWizard or an applied autoformat to

format your chart, and if your series is formatted with a line, the line

will disappear in the chart sample if you change the series so that it is

plotted on the secondary axis rather than on the primary axis.

Steps to Reproduce Behavior

---------------------------

1. In a new worksheet, enter the following data:

      A1: 10   B1: 20   C1: 30

      A2: 11   B2: 21   C2: 31

      A3: 12   B3: 22   C3: 32

2. Select the range A1:C3, and press F11 to run the ChartWizard.

3. In the Step 1 Of 5 screen, verify that the Range box contains the

   formula =$A$1:$C$3 and choose Next.

4. In the Step 2 Of 5 screen, under Select A Chart Type, choose XY

   (Scatter) and choose Next.

5. In the Step 3 Of 5 screen, under Select A Format For The XY (Scatter)

   Chart, choose format 2, and choose Next.

6. In the Step 4 Of 5 screen, choose Finish.

7. On the chart, select series 1, and from the Format menu, choose Selected

   Data Series. Select the Axis tab and under Plot Series On select the

   Secondary Axis option.

Series 1 is no longer displayed with a line in the sample chart. The

pattern format has changed just by changing the axis to plot the series on.

REFERENCES

==========

For more information about Applying Colors, Patterns, And Borders To

Chart Items, choose the Search button in Help and type:

   patterns

Additional reference words: 5.00


XL5: Objects Setting Applies to Entire Workbook

Article ID: Q107976

----------------------------------------------------------------------

The information in this article applies to:

- Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0, the Objects setting (located on the

View tab in the Options dialog box) can be set to one of three

options: Show All, Show Placeholders, or Hide All. The setting you

choose applies to all sheets in the current workbook.

In earlier versions of Microsoft Excel, the Objects setting is set for each

individual sheet.

Because of this difference in how Object options are set, when you open a

Microsoft Excel 4.0 workbook in Microsoft Excel 5.0, you may notice that

objects are visible when they should be hidden.

MORE INFORMATION

================

When you open a Microsoft Excel version 4.0 workbook in Microsoft Excel

5.0, the setting in the workbook that makes the most objects visible will

be applied to all of the sheets in the workbook. For example, if in 5.0 you

open a 4.0 workbook that contains the following sheets and settings

   Sheet    Object Setting

   -----------------------

   Sheet1   Hide All

   Sheet2   Show Placeholders

   Sheet3   Show All

all the sheets the will use the Show All option. If you open a workbook

with these settings

   Sheet    Object Setting

   -----------------------

   Sheet 1  Hide All

   Sheet 2  Show Placeholders

   Sheet 3  Hide All

all sheets will use the Show Placeholders option.

Additional reference words: 5.00


XL5: Unexpected Settings/Values If 4.0 Workbook Opened in 5.0

Article ID: Q107977

----------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

----------------------------------------------------------------------

SUMMARY

=======

In Microsoft Excel version 5.0, all sheets in a workbook share the

following calculation settings:

 - 1904 Date System

 - Precision as Displayed

 - Save External Link Values

In earlier versions of Microsoft Excel, these options were set individually

for each sheet.

Because of this difference, when you open a Microsoft Excel 4.0 workbook in

Microsoft Excel version 5.0, you may find that some of your sheets are

using the incorrect 1904 Date System, Precision as Displayed, or Save

External Link Values settings.

MORE INFORMATION

================

When you open a Microsoft Excel version 4.0 workbook in Microsoft

Excel version 5.0, the 1904 Date System, Precision as Displayed, and Save

External Link Values settings for the last sheet in the workbook are

applied to all of the sheets in the workbook. For example, if you have a

Microsoft Excel 4.0 Workbook that contains the following sheets and

settings

   Sheet    1904 Date  Precision as  Save External

   name     System     Displayed     Link Values

   -----------------------------------------------

   Sheet1   no         yes           yes

   Sheet2   yes        no            no

   Sheet3   no         no            yes

and you open the workbook in Microsoft Excel version 5.0, the sheets will

all use the settings specified the last sheet (Sheet3 in this example). If

Sheet2 is the last sheet in the workbook, its settings will be used by all

of the other sheets.

Additional reference words: 5.00


XL5: Data in AutoFill Series Incremented Based on Selection

Article ID: Q107999

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

The following are guidelines for using the AutoFill feature to create a

series of incremented values:

 - When you select an entire row or column and drag the fill handle, the

   values in the cells are incremented. To avoid incrementation, hold down

   the CTRL key when you drag the fill handle.

 - When you use the AutoFill feature to increment values, the values are

   incremented by 1 or by the difference between the values for that

   particular series.

 - When you drag the fill handle on a single cell that contains a number,

   the number is not incremented. To increment a number when a single cell

   is selected, hold down CTRL when you drag the fill handle.

 - When you drag the fill handle perpendicular to a series, the series is

   not incremented. An exception to this rule is when the series contains

   mixed types of data as in the following examples.

   Example 1

   ---------

   If the cells contain the following

      A1: 1

      A2: a

      A3: b

   when you select these cells and drag the fill handle to the right, the

   values will be incremented to:

      B1: 2

      B2: a

      B3: b

   Example 2

   ---------

   If you select the following cells (where cells A2 and A3 are blank)

      A1: 1

      A2:

      A3:

   and you drag the fill handle to the right, the result is:

      B1: 2

      B2:

      B3:

   If you do not want to increment the values in row 1, hold down CTRL when

   you drag the fill handle.

NOTE: The information in this article applies to AutoFill only when you are

using the fill handle. When you are filling cells with the Fill command on

the Edit menu, values are not necessarily incremented in the same manner.

REFERENCES

==========

"User's Guide," version 5.0, pages 114-123

Additional reference words: 5.00 5.0


XL5: Recording a Visual Basic Macro in an External Module

Article ID: Q108060

--------------------------------------------------------------------

The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0

--------------------------------------------------------------------

In Microsoft Excel 5.0, you can record a macro and save it in a different

workbook than the workbook you are performing the steps in. To do this,

follow the steps below to set the macro recorder to record in the other

workbook:

1. Select the Visual Basic module in the workbook where the macro should

   begin recording.

2. From the Tools menu, choose Record Macro, and choose Mark Position for

   Recording.

   NOTE: At this point you can return to the workbook in which you plan to

   perform the steps.

To begin recording:

1. From the Tools menu, choose Record Macro.

2. From the Tools menu, choose Record Macro, and choose Record at

   Mark.

NOTE: This method does not record the Sub() and End Sub necessary to create

a separate subroutine. This is by design. To create a separate subroutine,

add the Sub() and End Sub statements to the recorded code manually.

Additional reference words: 5.00